﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace Model
{
    public class ServerConnectDAO
    {
        /// <summary>
        /// 获取局域网中所有SQL Server实例
        /// </summary>
        /// <returns></returns>
        public List<string> GetSqlServerOverLan()
        {
            try
            {
                SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
                DataTable table = instance.GetDataSources();
                List<string> list = new List<string>();
                foreach (DataRow dr in table.AsEnumerable())
                {
                    list.Add(DBNull.Value.Equals(dr[1]) ? (dr[0].ToString()) : (String.Format("{0}\\{1}", dr[0], dr[1])));
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过实例名获取数据库列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetDatabaseNameFromServer(string serverName, string username,string password,enumVerifyMode verifyMode)
        {
            SqlConnection con=null;
            try
            {
                con=new SqlConnection(GetConnectString(serverName,"master",username, password,verifyMode));
                return DbHelperSQL.ReturnStringList
                   (
                    con, 
                    "SELECT name FROM master..sysdatabases", 
                    "name"
                    );
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                { 
                    con.Close(); 
                }
            }
        }

        /// <summary>
        /// 通过数据库名获取所有数据表
        /// </summary>
        /// <returns></returns>
        public List<string> GetTablesFromDataBase(string serverName,string dbName,string username, string password, enumVerifyMode verifyMode)
        {
            SqlConnection con = null;
            try
            {
                con = new SqlConnection(GetConnectString(serverName,dbName, username, password, verifyMode));
                return DbHelperSQL.ReturnStringList
                   (
                    con,
                    "select *  from  information_schema.tables",
                    "TABLE_NAME"
                    );
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }


        /// <summary>
        /// 通过数据库名获取所有数据表
        /// </summary>
        /// <returns></returns>
        public List<string> GetTablesFromDataBase(string conStr)
        {
            SqlConnection con = null;
            try
            {
                con = new SqlConnection(conStr);
                return DbHelperSQL.ReturnStringList
                   (
                    con,
                    "select *  from  information_schema.tables",
                    "TABLE_NAME"
                    );
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }


        /// <summary>
        /// 获取正确的连接字符串
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="verifyMode"></param>
        /// <returns></returns>
        public string GetConnectString(string serverName,string dbName, string username,string password, enumVerifyMode verifyMode)
        {
            if (verifyMode == enumVerifyMode.Windows)
            {
                return ConnectString(serverName, dbName);
            }
            else
            {
                return ConnectString(serverName, dbName, username, password);
            }

        }

        /// <summary>
        /// 拼接SQL登陆连接字符串
        /// </summary>
        /// <param name="Servername"></param>
        /// <param name="Database"></param>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        private string ConnectString(string Servername, string Database, string Username, string Password)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Data Source=");
            sb.Append(Servername);
            sb.Append(";Initial Catalog=");
            sb.Append(Database);
            sb.Append(";Persist Security Info=True;User ID=");
            sb.Append(Username);
            sb.Append(";Password=");
            sb.Append(Password);
            return sb.ToString();
        }
       

        /// <summary>
        /// 拼接Windows登陆连接字符串
        /// </summary>
        /// <param name="Servername"></param>
        /// <param name="Database"></param>
        /// <returns></returns>
        private string ConnectString(string Servername, string Database)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Data Source=");
            sb.Append(Servername);
            sb.Append(";Initial Catalog=");
            sb.Append(Database);
            sb.Append(";Integrated Security=True");
            return sb.ToString();
        }

       
    }
}
